/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.railway.reports.utils;

/**
 *
 * @author Kenny
 */
public class QueryBuilder {

    public static int MORE_THAN = 0;
    public static int LESS_THAN = 1;
    public static int MORE_THAN_EQUALS = 2;
    public static int LESS_THAN_EQUALS = 3;
    public static int NOT_EQUALS = 4;
    public static int ASC = 5;
    public static int DESC = 6;
    private StringBuilder sb;

    public QueryBuilder() {
        this.sb = new StringBuilder();
    }

    public QueryBuilder select(String... colName) {
        sb.append("SELECT ");
        if (colName.length != 0) {
            sb.append(colName[0]);
            for (int i = 1; i < colName.length; i++) {
                sb.append(", ").append(colName[i]);
            }
        } else {
            sb.append("*");
        }
        sb.append(" ");
        return this;
    }

    public QueryBuilder from(String tableName) {
        sb.append("FROM ").append(tableName);
        return this;
    }

    public QueryBuilder from(String... tableName) {
        sb.append("FROM ");
        for (int i = 0; i < tableName.length; i++) {
            String string = tableName[i];
            sb.append(string).append((i == tableName.length - 1 ? "" : ", "));
        }
        return this;
    }

    public QueryBuilder where(String condition) {
        sb.append(" WHERE ");
        return this;
    }

    public QueryBuilder where(String condition, Object data) {
        sb.append(" WHERE ").append(condition).append(" = ").append((data instanceof String ? "\"" + data + "\"" : data));
        return this;
    }

    public QueryBuilder where(String condition, Object data, boolean isString) {
        sb.append(" WHERE ").append(condition).append(" = ").append((isString ? "\"" + data + "\"" : data));
        return this;
    }

    public QueryBuilder where(String condition, Object data, int operator) {
        sb.append(" WHERE ").append(condition);
        switch (operator) {
            case 0:
                sb.append(" > ");
                break;
            case 1:
                sb.append(" < ");
                break;
            case 2:
                sb.append(" >= ");
                break;
            case 3:
                sb.append(" <= ");
                break;
            case 4:
                sb.append(" != ");
                break;
        }
        sb.append((data instanceof String ? "\"" + data + "\"" : data));
        return this;
    }

    public QueryBuilder in(boolean isString, Object... inExpr) {
        sb.append("IN (");
        if (isString) {
            for (int i = 0; i < inExpr.length; i++) {
                sb.append("\"").append(inExpr[i]).append("\"").append((i == inExpr.length ? "" : ", "));
            }
        } else {
            for (int i = 0; i < inExpr.length; i++) {
                sb.append(inExpr[i]).append((i == inExpr.length ? "" : ", "));
            }
        }
        sb.append(")");
        return this;
    }

    public QueryBuilder and(String moreCondition, Object data) {
        sb.append(" AND ").append(moreCondition).append(" = ").append((data instanceof String ? "\"" + data + "\"" : data));
        return this;
    }

    public QueryBuilder and(String moreCondition, Object data, boolean isString) {
        sb.append(" AND ").append(moreCondition).append(" = ").append((isString ? "\"" + data + "\"" : data));
        return this;
    }

    public QueryBuilder andBetween(String having, Object data, Object data2) {
        sb.append(" AND ")
                .append(having)
                .append(" BETWEEN ")
                .append((data instanceof String ? "\"" + data + "\"" : data))
                .append(" AND ")
                .append((data2 instanceof String ? "\"" + data2 + "\"" : data2));
        return this;
    }

    public QueryBuilder and(String moreCondition, Object data, int operator) {
        sb.append(" AND ").append(moreCondition);
        switch (operator) {
            case 0:
                sb.append(" > ");
                break;
            case 1:
                sb.append(" < ");
                break;
            case 2:
                sb.append(" >= ");
                break;
            case 3:
                sb.append(" <= ");
                break;
            case 4:
                sb.append(" != ");
                break;
        }
        sb.append((data instanceof String ? "\"" + data + "\"" : data));
        return this;
    }

    public QueryBuilder or(String moreCondition, Object data) {
        sb.append(" OR ").append(moreCondition).append(" = ").append((data instanceof String ? "\"" + data + "\"" : data));
        return this;
    }

    public QueryBuilder or(String moreCondition, Object data, int operator) {
        sb.append(" OR ").append(moreCondition);
        switch (operator) {
            case 0:
                sb.append(" > ");
                break;
            case 1:
                sb.append(" < ");
                break;
            case 2:
                sb.append(" >= ");
                break;
            case 3:
                sb.append(" <= ");
                break;
            case 4:
                sb.append(" != ");
                break;
        }
        sb.append((data instanceof String ? "\"" + data + "\"" : data));
        return this;
    }

    public QueryBuilder andFunction(String moreCondition, String dbFunction) {
        sb.append(" AND ").append(moreCondition).append(" = ").append(dbFunction);
        return this;
    }

    public QueryBuilder andFunction(String moreCondition, String dbFunction, int operator) {
        sb.append(" AND ").append(moreCondition);
        switch (operator) {
            case 0:
                sb.append(" > ");
                break;
            case 1:
                sb.append(" < ");
                break;
            case 2:
                sb.append(" >= ");
                break;
            case 3:
                sb.append(" <= ");
                break;
            case 4:
                sb.append(" != ");
                break;
        }
        sb.append(dbFunction);
        return this;
    }

    public QueryBuilder groupBy(String... groupExpr) {
        if (groupExpr.length == 1) {
            sb.append(" GROUP BY ").append(groupExpr[0]);
        } else {
            sb.append(" GROUP BY ").append(groupExpr[0]);
            for (int i = 1; i < groupExpr.length; i++) {
                sb.append(", ").append(groupExpr[i]);
            }
        }
        return this;
    }

    public QueryBuilder orderBy(String orderExpr, int order, boolean firstExpr) {
        if (firstExpr) {
            sb.append(" ORDER BY ").append(orderExpr).append((order == ASC ? " ASC" : " DESC"));
        } else {
            sb.append(", ").append(orderExpr).append((order == ASC ? " ASC" : " DESC"));
        }
        return this;
    }

    public QueryBuilder havingBetween(String having, Object data, Object data2) {
        sb.append(" HAVING ")
                .append(having)
                .append(" BETWEEN ")
                .append((data instanceof String ? "\"" + data + "\"" : data))
                .append(" AND ")
                .append((data2 instanceof String ? "\"" + data2 + "\"" : data2));
        return this;
    }

    public QueryBuilder having(String having, Object data) {
        sb.append(" HAVING ").append(having).append(" = ").append((data instanceof String ? "\"" + data + "\"" : data));
        return this;
    }

    public QueryBuilder having(String having, Object data, int operator) {
        sb.append(" HAVING ").append(having);
        switch (operator) {
            case 0:
                sb.append(" > ");
                break;
            case 1:
                sb.append(" < ");
                break;
            case 2:
                sb.append(" >= ");
                break;
            case 3:
                sb.append(" <= ");
                break;
            case 4:
                sb.append(" != ");
                break;
        }
        sb.append((data instanceof String ? "\"" + data + "\"" : data));
        return this;
    }

    public QueryBuilder limit(int row) {
        sb.append(" LIMIT ").append(row);
        return this;
    }

    public String build() {
        sb.append(";");
        return sb.toString();
    }
}
